package com.sinog.biz.schedule;

import com.sinog.biz.BizApplication;
import com.sinog.biz.mapper.havexml.AbPrisonerMapper;
import com.sinog.biz.mapper.havexml.AreaCodeFullMapper;
import com.sinog.biz.mapper.havexml.DepartmentMapper;
import com.sinog.biz.mapper.havexml.PrisonerMainMapper;
import com.sinog.biz.mapper.havexml.other.OldDataAsyncToBuMapper;
import com.sinog.biz.util.CodeUrlUtils;
import com.sinog.biz.util.CreateInsertSqlUtil;
import com.sinog.biz.util.DataSourceCheckUtils;
import com.sinog.core.domain.Department;
import com.sinog.core.utilbean.Constants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * @desc 各省数据同步到司法部 - 定时补偿 每天凌晨4点执行一次
 * @author lvjie
 * @date -
 */
@Slf4j
@Component
@Configuration
@ConditionalOnProperty(name = "gkzx.azbj.olddata.open", havingValue = "true")
public class OldDataSchedule {

    /**
     * 部级资源库
     */
    private JdbcTemplate bujiJdbcTemplate;
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private OldDataAsyncToBuMapper oldDataAsyncToBuMapper;
    @Autowired
    private PrisonerMainMapper prisonerMainMapper;
    @Autowired
    private AbPrisonerMapper abPrisonerMapper;
    @Autowired
    private AreaCodeFullMapper areaCodeFullMapper;
    @Autowired
    private DepartmentMapper departmentMapper;
    /**
     * 数据检查，同步至司法部
     * 需要更新的表自行添加
     */
    @Scheduled(cron = "${gkzx.azbj.olddata.cron}")
    private void asyncOldDataToBu() {
        log.info("=====================开始同步=========================");
        // 本省code
        String localCode = CodeUrlUtils.getLocalCode();
        // 数据源
        ApplicationContext applicationContext = BizApplication.applicationContext;
        bujiJdbcTemplate = (JdbcTemplate)applicationContext.getBean("bujiJdbcTemplate");
        jdbcTemplate = (JdbcTemplate)applicationContext.getBean("jdbcTemplate");
        //检测全部数据源是否可用
        boolean allRunning = DataSourceCheckUtils.checkRunningStatus(bujiJdbcTemplate,jdbcTemplate);
        if(true){
            // 需要更新的表集合
            HashMap<String,String> tableMaps = new HashMap<>(32);
            tableMaps.put("ab_anzhi","id");
            tableMaps.put("ab_bangfu","id");
            tableMaps.put("ab_cun_jianding","id");
            tableMaps.put("ab_genzongbangjiao","id");
            tableMaps.put("ab_jiu","id");
            tableMaps.put("ab_lieguan","id");
            tableMaps.put("ab_mv","id");
            tableMaps.put("ab_prisoners","id");
            tableMaps.put("ab_prisoners_jcab","id");
            tableMaps.put("ab_shehuijiuzhu","id");
            tableMaps.put("ab_tuoguan","id");
            tableMaps.put("ab_xiaozu","id");
            tableMaps.put("ab_zoufang","id");
            tableMaps.put("ab_zyzjiuzhu","id");
            tableMaps.put("azbj_jdgl","id");
            tableMaps.put("bjgz_ry","id");
            tableMaps.put("jypx_jg","id");
            tableMaps.put("jyxx_mk","id");
            tableMaps.put("prisave_temp","id");
            tableMaps.put("prisoner_cjpg","id");
            tableMaps.put("prisoner_fx","id");
            tableMaps.put("prisoner_hc","id");
            tableMaps.put("prisoner_main","id");
            tableMaps.put("prisoner_sr","id");
            tableMaps.put("prisoner_xjxx","id");
            tableMaps.put("prisoner_zn","id");
            tableMaps.put("prisoner_zz","id");
            tableMaps.put("shgyzz_xxgl","id");
            tableMaps.put("zfbz_xxgl","id");
            tableMaps.put("zyz_xxgl","id");
            long startTime = System.currentTimeMillis();
            for(Map.Entry<String,String> entry : tableMaps.entrySet()) {
                String tableName = entry.getKey();
                String searchStr = entry.getValue();
                // 上线时要将azbj-province-sfb改为azbj_judicature
                String sql = "select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name= '" + tableName + "' and table_schema = 'azbj_judicature'";
                List<String> columnsList = bujiJdbcTemplate.queryForList(sql,String.class);
                needAsyncPrinumList(tableName,searchStr,localCode,columnsList);
            }
            deleteDataFromBu(tableMaps,localCode);
            long endTime = System.currentTimeMillis();
            log.info("===================耗时:{},开始时间:{},结束时间:{}",(endTime - startTime) / 1000 / 60,startTime,endTime);
            log.info("=====================结束同步=========================");
        } else {
            log.error("检测到数据源不可用,本次执行【同步数据至司法部】任务失败!");
        }
    }

    /**
     * 批量插入数据
     * @param objects objects
     * @param tableName tableName
     * @param columnsList columnsList
     */
    private void batchUpdateObj(List<HashMap<String,Object>> objects,String tableName,List<String> columnsList) {
        log.info("=============" + tableName + "========批量插入数据开始=========================");
        List<Object[]> objList = new ArrayList<>(16);
        StringBuilder sqlStr = new StringBuilder(16);
        StringBuilder paramStr = new StringBuilder(16);
        sqlStr.append("insert into ").append(tableName).append(" (");
        if(null != objects && !objects.isEmpty()) {
            for(String columnName : columnsList) {
                sqlStr.append(columnName).append(",");
                paramStr.append("?").append(",");
            }
        }
        sqlStr.deleteCharAt(sqlStr.length() - 1);
        if(!StringUtils.isBlank(paramStr)) {
            paramStr.deleteCharAt(paramStr.length() - 1);
        }
        paramStr.append(")");
        sqlStr.append(") values (");
        sqlStr.append(paramStr);
        assert null != objects;
        for(HashMap<String,Object> dataMap : objects) {
            objList.add(CreateInsertSqlUtil.buildInsertSql3(dataMap,columnsList));
        }
        if(!objects.isEmpty()) {
            // 需要更新的表数据信息
            try {
                bujiJdbcTemplate.batchUpdate(sqlStr.toString(),objList);
                log.info("=============" + tableName + "========批量插入数据成功=========================");
            } catch(DataAccessException e) {
                log.error("=============" + tableName + "======insert异常=======================",e);
            }
        }
    }

    /**
     * 查询需要更新的数据集合
     * @param tableName 表名
     * @param searchStr 查询条件字段
     * @param localCode 本省code
     * @param columnsList columnsList
     */
    private void needAsyncPrinumList(String tableName,String searchStr,String localCode,List<String> columnsList) {
        List<HashMap<String,Object>> objects;
        try {
            // 司法部 已经存在的数据编号集合
            List<String> savePrinumList = bujiJdbcTemplate.queryForList("select " + searchStr + " as prinum from " + tableName + " where substr( " + searchStr + ",1,2) = " + localCode.substring(0,2),String.class);
            // 本省 已经存在的数据编号集合
            List<String> needPrinumList = oldDataAsyncToBuMapper.getNeedAsyncList(tableName,searchStr,localCode.substring(0,2));
            log.info("<<<===={}========司法部数据：{}条=========当前省数据：{}条=========>>>",tableName,savePrinumList.size(),needPrinumList.size());
            log.info("===start======================needPrinumList==" + needPrinumList.size() + "====");
            if(!savePrinumList.isEmpty()) {
                needPrinumList.removeAll(savePrinumList);
            }
            log.info("===end==================去重后====needPrinumList==" + needPrinumList.size() + "====");
            if(!needPrinumList.isEmpty()) {
                int p2 = 0;
                for(int i = 1; i <= ((needPrinumList.size() / Constants.Number.NUM_FIVE_THOUSAND) + 1); i++) {
                    List<String> subList;
                    if(i == ((needPrinumList.size() / Constants.Number.NUM_FIVE_THOUSAND) + 1)) {
                        subList = needPrinumList.subList(p2,needPrinumList.size());
                    } else {
                        subList = needPrinumList.subList(p2,i * Constants.Number.NUM_FIVE_THOUSAND);
                    }
                    objects = oldDataAsyncToBuMapper.getOldDataAllByTableName(tableName,searchStr,subList,localCode.substring(0,2));
                    log.info("==============<<<{}>>>需要同步数据,{}条==============",tableName,objects.size());
                    batchUpdateObj(objects,tableName,columnsList);
                    p2 = i * Constants.Number.NUM_FIVE_THOUSAND;
                }
            }
        } catch(DataAccessException e) {
            log.error("===================数据查询sql异常=======================",e);
        }
    }

    /**
     * 删除部级存在，省级不存在数据
     * @param tableMaps 表名集合
     * @param localCode 本省code
     */
    private void deleteDataFromBu(HashMap<String,String> tableMaps,String localCode) {
        log.info("=====================删除部级存在，省级不存在数据=========================");
        for(Map.Entry<String,String> entry : tableMaps.entrySet()) {
            String tableName = entry.getKey();
            String searchStr = entry.getValue();
            // 司法部 已经存在的数据编号集合
            String sql = "select " + searchStr + " as prinum from " + tableName + " where substr( " + searchStr + ",1,2) = " + localCode.substring(0,2);
            List<String> savePrinumList = bujiJdbcTemplate.queryForList(sql,String.class);
            // 本省 已经存在的数据编号集合
            List<String> needPrinumList = oldDataAsyncToBuMapper.getNeedAsyncList(tableName,searchStr,localCode.substring(0,2));
            savePrinumList.removeAll(needPrinumList);
            if(!savePrinumList.isEmpty()) {
                int p2 = 0;
                for(int i = 1; i <= (savePrinumList.size() / Constants.Number.NUM_ONE_THOUSAND) + 1; i++) {
                    try {
                        StringBuilder delSql = new StringBuilder("delete from ");
                        delSql.append(tableName).append(" where ").append(searchStr).append(" in (");
                        List<String> subList;
                        if(i == ((savePrinumList.size() / Constants.Number.NUM_ONE_THOUSAND) + 1)) {
                            subList = savePrinumList.subList(p2,savePrinumList.size());
                        } else {
                            subList = savePrinumList.subList(p2,i * Constants.Number.NUM_ONE_THOUSAND);
                        }
                        for(String prinum : subList) {
                            delSql.append("'").append(prinum).append("',");
                        }
                        delSql.deleteCharAt(delSql.length() - 1);
                        delSql.append(")");
                        // 删除
                        bujiJdbcTemplate.update(delSql.toString());
                    } catch(DataAccessException e) {
                        log.error("=====================删除数据异常=========================",e);
                    }
                }
            }
            log.info("==============<<<{}>>>删除数据：{}条==============",tableName,savePrinumList.size());
            log.info("=====================删除成功=========================");
        }
    }

    /**
     * 每天定时处理未删除的数据，
     * 从司法所的角度来看：如果行政区划四级或三级不正确，给他del_flag赋值为-2
     * 从监狱的角度来看：如果录入单位在系统中不存在，则也算脏数据，给它del_flag = '-3',如果系统中不存在录入单位，则所有角色都不应该看见
     * 排除自建省份
     */
    @Scheduled(cron = "${gkzx.azbj.census.cron}")
    public void handleDirtyData() {
        log.info("-----------------------------------------------------OldDataSchedule:handleDirtyData------处理脏数据开始-------------------------------------------");
        //存储所有脏数据的罪犯编号
        List<String> huji = new CopyOnWriteArrayList<>();
        //存储所有正常数据的罪犯编号
        List<String> normal = new CopyOnWriteArrayList<>();
        //存储所有本省录入的且录入单位不合法的数据
        List<String> danwei = new CopyOnWriteArrayList<>();
        //获取当前数据库中prisoner_main表中所有未被删除的数据的户籍地址和罪犯编号
        Integer count = prisonerMainMapper.selectPrisonerMainCount();
        //查询正确的行政区划：省-街道
        List<String> proToVill = areaCodeFullMapper.getProToVill();
        log.info("-----------------------------------------------------OldDataSchedule:handleDirtyData------查询正确的行政区划：省-街道数量：{}-------------------------------------------",proToVill.size());
        //查询正确的行政区划：省-区县（为街道为空的设置）
        List<String> proToCou = areaCodeFullMapper.getProToCou();
        log.info("-----------------------------------------------------OldDataSchedule:handleDirtyData------查询正确的行政区划：省-区县数量:{}（为街道为空的设置）-------------------------------------------",proToCou.size());
        //查询本省所有单位id
        List<String> departmentIds = departmentMapper.selectAllDepartmentIds();
        log.info("-----------------------------------------------------OldDataSchedule:handleDirtyData------查询本省所有部门数量:{}-------------------------------------------",departmentIds.size());
        //查询本省localCode编码前两位
        String localCodeStartTwo = CodeUrlUtils.getLocalCodeStartTwo();
        //10w条数据处理
        Integer cs = count/100000;
        Integer sycs = count - cs*100000;
        Integer zcs = cs+1;
        for (int a = 0; a < zcs; a += 1) {
            int pageIndex = a * 100000;
            int pagesize;
            if (a <= cs) {
                pagesize = 100000;
            } else {
                pagesize = sycs;
            }
            //获取当前数据库中prisoner_main表中所有未被删除的数据的户籍地址和罪犯编号
            log.info("-----------------------------------------------------OldDataSchedule:handleDirtyData------获取当前数据库中prisoner_main表中所有未被删除的数据的户籍地址和罪犯编号-------------------------------------------");
            List<Map<String, String>> maps = prisonerMainMapper.selectNativeAddressForDirtyData(pageIndex, pagesize);
            for(Map<String,String> map : maps) {
                //获取所需要的地址和罪犯编号
                String priNum = map.get("priNum");
                String nativepro = map.get("nativepro");
                String nativecity = map.get("nativecity");
                String nativecou = map.get("nativecou");
                String nativevill = map.get("nativevill");
                //flag:实际为del_flag标识，用于更新
                String flag = map.get("flag");
                //pdanwei:录入单位，只判断本省的数据
                String pdanwei = map.get("pdanwei");
                if (StringUtils.isBlank(pdanwei)) {
                    danwei.add(priNum);
                }else {
                    //是本省录入的数据，且录入单位不合法
                    if (localCodeStartTwo.equals(priNum.substring(0,2)) && localCodeStartTwo.equals(pdanwei.substring(0,2)) && !departmentIds.contains(pdanwei)) {
                        danwei.add(priNum);
                    }
                }

                String address;
                if (StringUtils.isNotEmpty(nativevill)) {
                    address = new StringBuilder().append(nativepro).append(nativecity).append(nativecou).append(nativevill).toString();
                    if (proToVill.contains(address)) {
                        //如果原本不是正常数据，则更新为正常数据
                        if (!Constants.NumberStr.NUM_STR_ZERO.equals(flag)) {
                            normal.add(priNum);
                        }
                    }else {
                        //如果原本不是-2的脏数据，则更新为del_flag='-2'
                        if (!Constants.NumberStr.NUM_STR_FU_TEO.equals(flag)) {
                            huji.add(priNum);
                        }
                    }
                }else {
                    address = new StringBuilder().append(nativepro).append(nativecity).append(nativecou).toString();
                    if (proToCou.contains(address)) {
                        //如果原本不是正常数据，则更新为正常数据
                        if (!Constants.NumberStr.NUM_STR_ZERO.equals(flag)) {
                            normal.add(priNum);
                        }
                    }else {
                        //如果原本不是-2的脏数据，则更新为del_flag='-2'
                        if (!Constants.NumberStr.NUM_STR_FU_TEO.equals(flag)) {
                            huji.add(priNum);
                        }
                    }
                }
            }
        }
        int size = huji.size();
        int normalSize = normal.size();
        int danweiSize = danwei.size();
        log.info("-----------------------------------------------------OldDataSchedule:转为脏数据------需要更新的人员有:{}个-------------------------------------------", size);
        if (size < Constants.Number.NUM_ONE_THOUSAND) {
            prisonerMainMapper.batchUpdateDelFlag(huji,Constants.NumberStr.NUM_STR_FU_TEO);
        } else {
            //批量更新
            int i = 0;
            int k = size / Constants.Number.NUM_ONE_THOUSAND;
            do {
                int j = i + Constants.Number.NUM_ONE_THOUSAND;
                List<String> subList = huji.subList(i, j);
                prisonerMainMapper.batchUpdateDelFlag(subList,Constants.NumberStr.NUM_STR_FU_TEO);
                i = j;
            } while (--k > 0);
            List<String> subList2 = huji.subList(i, size);
            prisonerMainMapper.batchUpdateDelFlag(subList2,Constants.NumberStr.NUM_STR_FU_TEO);
        }
        log.info("-----------------------------------------------------OldDataSchedule:转为正常的人员------需要更新的人员有:{}个-------------------------------------------", normalSize);
        if (normalSize < Constants.Number.NUM_ONE_THOUSAND) {
            prisonerMainMapper.batchUpdateDelFlag(huji,Constants.NumberStr.NUM_STR_ZERO);
        } else {
            //批量更新
            int i = 0;
            int k = normalSize / Constants.Number.NUM_ONE_THOUSAND;
            do {
                int j = i + Constants.Number.NUM_ONE_THOUSAND;
                List<String> subList = normal.subList(i, j);
                prisonerMainMapper.batchUpdateDelFlag(subList,Constants.NumberStr.NUM_STR_ZERO);
                i = j;
            } while (--k > 0);
            List<String> subList2 = normal.subList(i, normalSize);
            prisonerMainMapper.batchUpdateDelFlag(subList2,Constants.NumberStr.NUM_STR_ZERO);
        }

        log.info("-----------------------------------------------------OldDataSchedule:处理本省录入单位不合法------需要更新的人员有:{}个-------------------------------------------", danweiSize);
        if (danweiSize < Constants.Number.NUM_ONE_THOUSAND) {
            prisonerMainMapper.batchUpdateDelFlag(huji,Constants.NumberStr.NUM_STR_FU_THREE);
        } else {
            //批量更新
            int i = 0;
            int k = danweiSize / Constants.Number.NUM_ONE_THOUSAND;
            do {
                int j = i + Constants.Number.NUM_ONE_THOUSAND;
                List<String> subList = danwei.subList(i, j);
                prisonerMainMapper.batchUpdateDelFlag(subList,Constants.NumberStr.NUM_STR_FU_THREE);
                i = j;
            } while (--k > 0);
            List<String> subList2 = danwei.subList(i, danweiSize);
            prisonerMainMapper.batchUpdateDelFlag(subList2,Constants.NumberStr.NUM_STR_FU_THREE);
        }
        log.info("-----------------------------------------------------OldDataSchedule:handleDirtyData处理结束-------------------------------------------");
    }

    /**
     * todo: 这个功能应该写在dev分支上
     * 每天定时处理重复数据
     * 处理规则：
     * 旧数据和新数据，如果旧数据是脏数据，则隐藏旧数据
     * 这个脏数据包含的内容不仅仅是户籍地址、单位不正确
     * 应该还包含基本信息和服刑信息中必填项没有填写完整
     * 基本信息中必填项：姓名、性别、证件类型、民族、证件号码、入监日期、文化程度、户籍类型、户籍地址
     * 服刑信息中必填项：罪名、犯罪类型、现刑期、附加刑、刑期起日、刑期止日、案犯类别、逮捕机关、是否累惯犯、是否残疾、捕前职业
     */
    public void handleRepeatData() {
        log.info("-----------------------------------------------------OldDataSchedule:handleRepeatData处理开始-------------------------------------------");
    }
}